package com.netcracker.studportal.server.dao.impl;

import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.hibernate.LockMode;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Expression;

import com.netcracker.studportal.server.model.*;
import com.netcracker.studportal.server.util.HibernateUtil;
import com.netcracker.studportal.server.dao.ExamDAO;
import com.netcracker.studportal.server.dao.ExamWithResultDAO;

public class ExamWithResultDAOImpl implements ExamWithResultDAO {
	private static final Logger LOG = Logger.getLogger(ExamWithResultDAOImpl.class);

	@Override
	public void Add(ExamWithResult exam) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			session.save(exam);
			session.getTransaction().commit();
		} catch (Exception e) {
			LOG.error("Error: ", e);
			// TODO delete it, show errors in log
			e.printStackTrace();
			throw new Exception(e);
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}
	}

	@Override
	public void Update(ExamWithResult exam) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			session.update(exam);
			session.getTransaction().commit();
		} catch (Exception e) {
			LOG.error("Error: ", e);
			// TODO delete it, show errors in log
			e.printStackTrace();
			throw new Exception(e);
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}
	}

	@Override
	public ExamWithResult getById(Long exam_id, Long student_id)
			throws Exception {
		Session session = null;
		ExamWithResult exam = null;
		try {
			session = HibernateUtil.getSessionFactory().openSession();
			exam = (ExamWithResult) session.createSQLQuery(
						"select * from exam_results where exam_id="	+ exam_id + " AND student_id=" + student_id)
						.addEntity(ExamWithResult.class).uniqueResult();
		} catch (Exception e) {
			LOG.error("Error: ", e);
			// TODO delete it, show errors in log
			e.printStackTrace();
			throw new Exception(e);
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}
		return exam;
	}

	@Override
	public void Delete(ExamWithResult exam) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			session.delete(exam);
			session.getTransaction().commit();
		} catch (Exception e) {
			LOG.error("Error: ", e);
			// TODO delete it, show errors in log
			e.printStackTrace();
			throw new Exception(e);
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}
	}

	@Override
	public Collection<ExamWithResult> getAll() throws Exception {
		Session session = null;
		List<ExamWithResult> exam = new ArrayList<ExamWithResult>();
		try {
			session = HibernateUtil.getSessionFactory().openSession();
			exam = session.createCriteria(ExamWithResult.class).list();
		} catch (Exception e) {
			LOG.error("Error: ", e);
			// TODO delete it, show errors in log
			e.printStackTrace();
			throw new Exception(e);
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}
		return exam;
	}
	
	@Override
	public List<Map<String,String>> getStudentResultsByExamID(Long exam_id) throws Exception{
		Session session = null;
		try{
			session = HibernateUtil.getSessionFactory().openSession();
			
			SQLQuery q = session.createSQLQuery(
					"SELECT er.student_id, " +
					"       s.first_name || ' ' || s.last_name," +
					"       er.mark, " +
					"       er.note, " +
					"       s.course," + 
					"       s.group_num" +
					"  FROM exam_results er, students s " + 
					"  WHERE s.student_id=er.student_id and er.exam_id=" + exam_id);
			List<Object []> list = q.list();
			List<Map<String,String>> map_list = new ArrayList<Map<String,String>>();
			for(Object[] obj : list){
				Map<String,String> m = new HashMap<String,String>();
				m.put("student_id", obj[0].toString());
				m.put("student_name", obj[1].toString());
				String mark = (obj[2] != null) ? obj[2].toString() : "";
				m.put("mark", mark);
				String note = (obj[3] != null) ? obj[3].toString() : "";
				m.put("note", note);
				String course = obj[4].toString();
				m.put("course", course);
				String groupNum = obj[5].toString();
				m.put("group_num", groupNum);
				map_list.add(m);
			}
			return map_list;
			
		}catch (Exception e) {
			LOG.error("Error: ", e);
			// TODO delete it, show errors in log
			e.printStackTrace();
			throw new Exception(e);
		} finally {
			if (session != null && session.isOpen()) {
				session.close();
			}
		}
		
	}

	/**
	 * Realization of singleton.
	 */
	private ExamWithResultDAOImpl() {}
	private static ExamWithResultDAO instance;
	public static ExamWithResultDAO getInstance() {
		if (instance == null) {
			instance = new ExamWithResultDAOImpl();
		}
		return instance;
	}
}
